package com.ruoyi.generator.util;

import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.generator.domain.CustomizeDBConfig;
import com.ruoyi.generator.domain.GenTable;
import com.ruoyi.generator.domain.GenTableColumn;
import org.apache.commons.compress.utils.Lists;

import java.sql.*;
import java.util.Date;
import java.util.List;

public class CreateDataSource {
    private static final String SQL = "select table_name, table_comment, create_time, update_time from information_schema.tables where table_schema = (select database())";
    private static String sql_ = "select table_name, table_comment, create_time, update_time from information_schema.tables " +
            "where table_schema = (select database()) and table_name in (%s)";


    String saa = "SELECT t.table_id, t.table_name, t.table_comment, t.sub_table_name, t.sub_table_fk_name, t.class_name, t.tpl_category, t.package_name, t.module_name, t.business_name, t.function_name, t.function_author, t.gen_type, t.gen_path, t.options, t.remark,\n" +
            "\t\t\t   c.column_id, c.column_name, c.column_comment, c.column_type, c.java_type, c.java_field, c.is_pk, c.is_increment, c.is_required, c.is_insert, c.is_edit, c.is_list, c.is_query, c.query_type, c.html_type, c.dict_type, c.sort\n" +
            "\t\tFROM gen_table t\n" +
            "\t\t\t LEFT JOIN gen_table_column c ON t.table_id = c.table_id\n" +
            "\t\twhere t.table_name = %s order by c.sort";

    //查询列信息

    String col = "select column_name, " +
            "(case when (is_nullable = 'no' && column_key != 'PRI') then '1' else null end) as is_required, " +
            "(case when column_key = 'PRI' then '1' else '0' end) as is_pk, " +
            "ordinal_position as sort, " +
            "column_comment, " +
            "(case when extra = 'auto_increment' then '1' else '0' end) as is_increment, " +
            "column_type\n" +
            "\t\tfrom information_schema.columns where table_schema = (select database()) and table_name = '%s'\n" +
            "\t\torder by ordinal_position";

    private CustomizeDBConfig config;

    public CreateDataSource(CustomizeDBConfig config) {
        this.config = config;
    }
    public List<GenTable> getCustomizeDBTables() throws SQLException {
        return getCustomizeDBTables("");
    }
    //执行查询操作
    Connection conn = null;
    public ResultSet executeQuery(String statement){
        ResultSet resultSet = null;
        try {
            conn = DriverManager.getConnection(config.getUrl(), config.getUsername(), config.getPassword());
            Statement smt = conn.createStatement();
            if (conn != null) {
                System.out.println("数据库连接成功！");
                resultSet = smt.executeQuery(statement);
                System.out.println("查询成功！");
            }
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        return resultSet;
    }
    public List<GenTableColumn> getCustomizeDBTableColums(String tableName) throws SQLException {
        ResultSet resultSet = executeQuery(String.format(col,tableName));
        List<GenTableColumn> columns = Lists.newArrayList();
        try {
            while(resultSet.next()){
                GenTableColumn column = new GenTableColumn();
                column.setColumnName(resultSet.getString("column_name"));
                column.setIsRequired(resultSet.getString("is_required"));
                column.setIsPk(resultSet.getString("is_pk"));
                column.setSort(resultSet.getInt("sort"));
                column.setColumnComment(resultSet.getString("column_comment"));
                column.setIsIncrement(resultSet.getString("is_increment"));
                column.setColumnType(resultSet.getString("column_type"));
                columns.add(column);
            }
        } finally {
            closeConn();
        }
        return columns;
    }
    public void closeConn(){
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public List<GenTable> getCustomizeDBTables(String statement) {
        List<GenTable> tableList = Lists.newArrayList();
        try {
            System.out.println("数据库连接成功！");
            String sql = SQL;
            if(!StringUtils.isEmpty(statement)){
                sql = String.format(sql_,statement);
            }
            ResultSet resultSet = executeQuery(sql);
            while(resultSet.next()){
                String table_name = resultSet.getString("table_name");
                String table_comment = resultSet.getString("table_comment");
                Date create_time = resultSet.getDate("create_time");
                Date update_time = resultSet.getDate("update_time");
                GenTable table = new GenTable();
                table.setTableName(table_name);
                table.setTableComment(table_comment);
                table.setCreateTime(create_time);
                table.setUpdateTime(update_time);
                tableList.add(table);
            }
            System.out.println("查询成功！");
        } catch (SQLException e1) {
            e1.printStackTrace();
        }finally {
            closeConn();
        }
        return tableList;
    }
}



